CREATE TABLE krankenkasse (
  id NUMBER(4)   NOT NULL ,
  name VARCHAR2(54)    ,
  privat CHAR(1)  DEFAULT 'N' NOT NULL   ,
PRIMARY KEY(id));



CREATE TABLE praxis (
  id NUMBER(6)   NOT NULL ,
  name VARCHAR2(108)   NOT NULL ,
  strasse VARCHAR2(54)    ,
  plz VARCHAR2(5)    ,
  ort VARCHAR2(52)   NOT NULL ,
  tel VARCHAR2(32)      ,
PRIMARY KEY(id));



CREATE TABLE arzt (
  id NUMBER(6)   NOT NULL ,
  praxis_id NUMBER(6)    ,
  name VARCHAR2(108)   NOT NULL ,
  vorname VARCHAR2(54)    ,
  fachgebiet VARCHAR2(54)      ,
PRIMARY KEY(id)  ,
  FOREIGN KEY(praxis_id)
    REFERENCES praxis(id));



CREATE TABLE patient (
  id NUMBER(10)   NOT NULL ,
  arzt_id NUMBER(6)    ,
  krankenkasse_id NUMBER(4)   NOT NULL ,
  geburtsdatum DATE   NOT NULL ,
  name VARCHAR2(108)    ,
  vorname VARCHAR2(54)    ,
  strasse VARCHAR2(54)    ,
  plz VARCHAR2(5)    ,
  ort VARCHAR2(52)    ,
  tel VARCHAR2(32)    ,
  vorgeschichte CLOB      ,
PRIMARY KEY(id)    ,
  FOREIGN KEY(krankenkasse_id)
    REFERENCES krankenkasse(id),
  FOREIGN KEY(arzt_id)
    REFERENCES arzt(id));



CREATE TABLE praxis_patient (
  praxis_id NUMBER(6)   NOT NULL ,
  patient_id NUMBER(10)   NOT NULL   ,
PRIMARY KEY(praxis_id, patient_id)    ,
  FOREIGN KEY(praxis_id)
    REFERENCES praxis(id),
  FOREIGN KEY(patient_id)
    REFERENCES patient(id));



CREATE TABLE behandlung (
  id NUMBER(10)   NOT NULL ,
  arzt_id NUMBER(6)   NOT NULL ,
  patient_id NUMBER(10)   NOT NULL ,
  von DATE   NOT NULL ,
  bis DATE    ,
  beschreibung CLOB      ,
PRIMARY KEY(id)    ,
  FOREIGN KEY(patient_id)
    REFERENCES patient(id),
  FOREIGN KEY(arzt_id)
    REFERENCES arzt(id));
